<?php
include_once $_SERVER ['DOCUMENT_ROOT'] . '/ad/sys/com/model/db/BaseDAO.php';
include_once $_SERVER ['DOCUMENT_ROOT'] . '/ad/sys/com/model/util/plog/classes/plog.php';

class PublishClickedAdDAO extends BaseDAO {
	
	public function __construct() {
		parent::__construct ();
		$this->log = Plog::factory ( __FILE__ );
	}
	public function insertData($attr){
		$sql=$this->genInserSQL('clickedad',$attr);
		$result = mysql_query ( $sql );
		return $result;
	}
	public function queryReport($account,$dateEnd){
		date_default_timezone_set('Asia/Taipei');
		$dateEnd2 = strtotime($dateEnd);
		$years = date("Y",$dateEnd2); //用date()函式取得目前年份格式0000
		$months = date("m",$dateEnd2); //用date()函式取得目前月份格式00
		$days = date("d",$dateEnd2); //用date()函式取得目前日期格式00
		$dateStart=	date("Y-m-d",mktime(0,0,0,$months,$days-30,$years));
		$dateEnd2=date("Y-m-d",mktime(0,0,0,$months,$days+1,$years));
		$sql="
			SELECT 
			DATE_FORMAT(c.CreateTime,'%Y/%m/%d') Date,
			SUM(PayMoney) Sum 
			FROM clickedad c 
				JOIN appinfo A ON c.Appid=A.id 
					WHERE A.account='$account' and c.CreateTime between
					DATE_FORMAT('$dateStart','%Y/%m/%d') and DATE_FORMAT('$dateEnd2','%Y/%m/%d')
					GROUP BY Date order by Date";
//		echo "sql=$sql";
		$result = mysql_query ( $sql );
		return $this->toArrayForClickedAd($result,$dateStart,$dateEnd);
		
	}
	
	function toArrayForClickedAd($result,$dateStart,$dateEnd) {
	    $row_set = array();
	    date_default_timezone_set('Asia/Taipei');
	    $tempDate=strtotime($dateStart);
	    $fetchDate=null;
		while ($row = @mysql_fetch_object($result)) {
			$fetchDate=strtotime($row->Date);
//			echo  date("m/d",$fetchDate)."<br/>";
			while($tempDate<$fetchDate){
				$row_set[]=array("Date"=>date("m/d",$tempDate),"Sum"=>0);
				$tempDate=strtotime(date("Y/m/d",mktime(0,0,0,date("m",$tempDate),date("d",$tempDate)+1,date("Y",$tempDate))));
			}
			$row_set[] = array("Date"=>date("m/d",$fetchDate),"Sum"=>$row->Sum);
			$tempDate=strtotime(date("Y/m/d",mktime(0,0,0,date("m",$fetchDate),date("d",$fetchDate)+1,date("Y",$fetchDate))));
	    } 
	    while($tempDate<=strtotime($dateEnd)){
//	    	echo date("m/d",$tempDate);
	    	$row_set[]=array("Date"=>date("m/d",$tempDate),"Sum"=>0);
			$tempDate=strtotime(date("Y/m/d",mktime(0,0,0,date("m",$tempDate),date("d",$tempDate)+1,date("Y",$tempDate))));
	    }
		@mysql_free_result($result);
		return $row_set;
	}
	
	public function getTodayPay($account){
		$today=@date("Y-m-d");
		$sql="SELECT SUM(PayMoney) Sum FROM clickedad C JOIN appinfo A ON C.APPID=A.ID WHERE DATE_FORMAT(C.CREATETIME,'%Y/%m/%d')=DATE_FORMAT('$today','%Y/%m/%d') AND Account='$account'";
//		echo $sql;
		$result = mysql_query ( $sql );
		$row=mysql_fetch_assoc($result);
		$sum=$row["Sum"];
		@mysql_free_result($result);
//		echo "sum=$sum";
		if(!isset($sum)){
			$sum=0;
		}
		return $sum;
	}
	
	
	public function getStatisticsCountDate($id,$date,$status){
		date_default_timezone_set('Asia/Taipei');
		$dateStart=strtotime($date."-01");
//		echo "dateStart=$dateStart";
		$dateEnd=date("Y/m/d",mktime(0,0,0,date("m",$dateStart)+1,date("d",$dateStart)-1,date("Y",$dateStart)));
		$dateStart=date("Y/m/d",mktime(0,0,0,date("m",$dateStart),date("d",$dateStart),date("Y",$dateStart)));
//		echo "dateEnd=$dateEnd";
//		echo "dateStart=$dateStart";
		$sql="SELECT COUNT(*) Sum ,DATE_FORMAT(CREATETIME,'%Y%m%d') Date FROM clickedad WHERE STATUS=$status AND APPID=$id AND DATE_FORMAT(CREATETIME,'%Y/%m/%d')>=DATE_FORMAT('$dateStart','%Y/%m/%d') AND DATE_FORMAT(CREATETIME,'%Y/%m/%d') < DATE_FORMAT('$dateEnd','%Y/%m/%d') GROUP BY DATE";
//		echo $sql;
		$result = mysql_query ( $sql );
		return $this->toArrayForClickedAd($result,$dateStart,$dateEnd);
	}
	
	public function getStatisticsRatioDate($id,$date,$status){
		date_default_timezone_set('Asia/Taipei');
		$dateStart=strtotime($date."-01");
		$dateEnd=date("Y/m/d",mktime(0,0,0,date("m",$dateStart)+1,date("d",$dateStart)-1,date("Y",$dateStart)));
		$dateStart=date("Y/m/d",mktime(0,0,0,date("m",$dateStart),date("d",$dateStart),date("Y",$dateStart)));
		
		$count=$this->getStatisticsCountDate($id,$date,$status);
		
		$sql="SELECT COUNT(*) Sum ,DATE_FORMAT(CREATETIME,'%Y%m%d') Date FROM clickedad
		 WHERE STATUS=$status
		 AND DATE_FORMAT(CREATETIME,'%Y/%m/%d')>=DATE_FORMAT('$dateStart','%Y/%m/%d') 
		 AND DATE_FORMAT(CREATETIME,'%Y/%m/%d') < DATE_FORMAT('$dateEnd','%Y/%m/%d') 
		 GROUP BY DATE";
//		echo $sql;
		$result = mysql_query ( $sql );
		$result = $this->toArrayForClickedAd($result,$dateStart,$dateEnd);
		for($i=0;$i<count($result);$i++){
			if($result[$i]["Sum"]!=0){
//				echo "before=".$result[$i]['Sum'].",".$count[$i]["Sum"]."<br />";
				$result[$i]["Sum"]=(double)($count[$i]["Sum"]*(100)/$result[$i]["Sum"]);
//				echo "after=".$result[$i]['Sum']."<br />";
			}
		}
		return $result;
	}
}
//$publishPayToUserDAO=new PublishClickedAdDAO();
//echo $publishPayToUserDAO->queryReport("koko0809@gmail.com","2011-08-30");
?>